import mssql
import config
from typing import Dict
from base import getDiagIdByCode
from register import setRegister

def query(sql):
    db = mssql.ZgqMssql(config.get('db'))
    dataList = db.query(sql)
    db.close()
    return dataList
    
def add(tableName: object, addData: object, way: object = 'insert', getsql: object = False):
    db = mssql.ZgqMssql(config.get('db'))
    return db.add(tableName, addData, way, getsql)
    db.close()
    
def update(tableName, data, where, getsql=False):
    db = mssql.ZgqMssql(config.get('db'))
    return db.update(tableName, data, where, getsql)
    db.close()
    
# 根据医生工号获取一个新的外配处方ID
def getPresId(doct_id: int):
    sql = 'SELECT MAX(pres_id) pres_id from pres_out where pres_id like \'9' + "{:04d}".format(doct_id) + '%\''
    list = query(sql)
    presId = '9' + "{:04d}".format(doct_id) + '0000000'
    if list[0]['pres_id']:
        presId = list[0]['pres_id']
    return str(int(presId) + 1)
  
# 获取审方药师ID
def getPresOutCheckId():
    sql = "SELECT csz FROM sys_xtsz WHERE CSMC = 'PRES_OUT_CHECK_ID'"
    list = query(sql)
    if list[0]['csz']:
        csz = list[0]['csz']
    return csz
    
# 根据医生工号获取一个新的外配处方ID
def getPresId(doct_id: int):
    sql = 'SELECT MAX(pres_id) pres_id from pres_out where pres_id like \'9' + "{:04d}".format(doct_id) + '%\''
    list = query(sql)
    presId = '9' + "{:04d}".format(doct_id) + '0000000'
    if list[0]['pres_id']:
        presId = list[0]['pres_id']
    return str(int(presId) + 1)

# 保存处方信息
def savePres(data: Dict):
    fields = { 'pres_id': 'pres_id', 'patient_id': 'patient_id', 'sect_id': 'sect_id',
        'pres_count': 'pres_count', 'amount': 'amount', 'charge_cat_id': 'charge_cat_id', 'check_id': 'check_id',
        'fact_amount': 'amount', 'pres_date': 'pres_date', 'doct_id': 'doct_id', 'medi_count': 'medi_count',
        'register_pres': 'register_pres', 'icds': 'icds', 'icdnames': 'icdnames', 'sts': 'sts', 'valid_days': 'valid_days' }
    medi_fields = { 'medi_mark_id':'medi_mark_id', 'name':'name', 'pack':'pack', 'unit_id':'unit_id',
        'times':'times', 'quantity':'quantity', 'count_quantity':'count_quantity', 'resale_price':'resale_price',
        'amount':'amount', 'medi_use':'medi_use', 'use_type_id':'use_type_id', 'medi_doage':'medi_doage',
        'medi_unit_id':'medi_unit_id', 'medi_pass': 'medi_pass' }
    
    # 初始化处方保存数据
    saveData = mssql.mapData(data, fields)
    # 规范化icds
    icds = saveData['icds']
    if len(icds) > 0:
        saveData['icds'] = '' + ','.join(icds) + ''
    else:
        saveData['icds'] = ''
    # diag_id,根据第一个icds
    saveData['diag_id'] = getDiagIdByCode(icds[0])
    # pres_type, 缺省O
    saveData['pres_type'] = 'O'
    # 审方药师
    saveData['check_id'] = getPresOutCheckId()
    # 判断状态
    if not saveData.get('sts'):
        saveData['sts'] = 'C'
    # 处方药品数量medi_count
    saveData['medi_count'] = len(data['list'])
    # 开始保存数据
    db = mssql.ZgqMssql(config.get('db'))
    # 保存处方主表
    if saveData.get('pres_id'):
        sql = db.update('pres_out', saveData, 'pres_id = \'' + saveData.get('pres_id') + '\'', False)
    else:
        saveData['pres_id'] = getPresId(saveData['doct_id'])
        sql = db.add('pres_out', saveData, 'insert', False)
    # 删除处方明细
    sql = 'delete from pres_medi_out where pres_id = \'' + saveData['pres_id'] + '\''
    rowcount = db.execute(sql)
    # 添加处方明细
    list = data['list']
    for index in range(len(list)):
        # 基本数据字段
        saveMediData = mssql.mapData(list[index], medi_fields)
        # pres_id
        saveMediData['pres_id'] = saveData['pres_id']
        saveMediData['seq'] = index + 1
        sql = db.add('pres_medi_out', saveMediData, 'insert', False)
    # 向数据库提交
    db.commit()
    # 关闭数据库连接
    db.close()
    return {
        "code": 200,
        "msg": 'success',
        "saveData": saveData,
        "data": {
            "pres_id": saveData['pres_id']
        }
    }
    
# 获取医生外配处方列表
def getPresOutList(data: Dict):
    sql = 'SELECT ' + \
        '\n pres_out.pres_id,' + \
        '\n pres_out.pres_date, ' + \
        '\n patient.patient_id,' + \
        '\n convert(nvarchar(1000),patient.name) patient_name, ' + \
        '\n convert(nvarchar(1000),staff_member.name) doct_name, ' + \
        '\n convert(nvarchar(1000),sect.name) sect_name, ' + \
        '\n pres_out.amount ' + \
        '\n FROM pres_out inner join patient ON pres_out.patient_id = patient.patient_id ' + \
        '\n INNER JOIN sect ON sect.sect_id = pres_out.sect_id ' + \
        '\n INNER JOIN staff_member ON pres_out.doct_id = staff_member.staff_id ' + \
        '\n WHERE 1 = 1'
    if data.get('doct_id'):
        sql = sql + '\n and pres_out.doct_id = ' + str(data.get('doct_id'))
    if data.get('pres_id'):
        sql = sql + "\n and pres_out.pres_id = '{}'".format(data.get('pres_id'))
    if data.get('patient_id'):
        sql = sql + '\n and pres_out.patient_id = ' + str(data.get('patient_id'))
    if data.get('card_nbr'):
        sql = sql + "\n and patient.card_nbr like '%{}%'".format(data.get('card_nbr'))
    dateScope = data.get('pres_date')
    if not dateScope:
        dateScope = []
    if len(dateScope) == 2:
        sql = sql + '\n and pres_out.pres_date BETWEEN \'' + dateScope[0] + '\' AND \'' + dateScope[1] + ' 23:59:59\' '
    sql = sql + '\n  ORDER BY pres_out.pres_date desc'
    list = query(sql)
    return {
        "code": 200,
        "msg": 'success',
        "data": {
            "dataList": list
        }
    }
    
# 根据pres_id获取详细信息
def getPresDetail(pres_id: str):
    # 打开数据库连接
    db = mssql.ZgqMssql(config.get('db'))
    # 获取外配处方主表信息
    sql = 'SELECT pres_out.pres_id,' + \
        ' patient.patient_id, convert(nvarchar(1000),patient.name) patient_name,' + \
        ' convert(nvarchar(10),case when patient.sex = 1 THEN \'男\' ELSE \'女\' end) patient_sex, ' + \
        ' patient.age patient_age, patient.card_nbr patient_id_card,' + \
        ' convert(nvarchar(1000),patient.addr) patient_addr,' + \
        ' case when patient.contact_tel is null then \'\' else patient.contact_tel end patient_contact_tel,' + \
        ' sect.sect_id, convert(nvarchar(100),sect.name) sect_name,' + \
        ' staff_member.staff_id doct_id, convert(nvarchar(100),staff_member.name) doctor_name,' + \
        ' pres_out.amount, pres_out.diag_id, pres_out.pres_date,' + \
        ' pres_out.icds, convert(nvarchar(1000),pres_out.icdnames) icdnames,' + \
        ' convert(int,pres_out.valid_days) valid_days,pres_out.hi_rxno,pres_out.rx_trace_code,pres_out.rx_stas_codg' + \
        ' FROM pres_out inner join patient on pres_out.patient_id = patient.patient_id' + \
        ' INNER JOIN sect ON pres_out.sect_id = sect.sect_id' + \
        ' INNER JOIN staff_member ON staff_member.staff_id = pres_out.doct_id' + \
        ' WHERE pres_out.pres_id = \'' + pres_id + '\''
    pres = db.query(sql)
    if len(pres) == 1:
        pres[0]['icds'] = pres[0]['icds'].split(',')
        ret = {
            "code": 200,
            "msg": "成功！",
            "data": pres[0]
        }
        # 获取外配处方明细记录
        sql = 'SELECT pres_id,seq,convert(nvarchar(1000),pres_medi_out.name) name,' + \
            ' pres_medi_out.medi_mark_id,convert(nvarchar(1000),medi_mark.spec) spec, ' + \
            ' pres_medi_out.quantity,pres_medi_out.resale_price,pres_medi_out.amount,pres_medi_out.zxts,' + \
            ' pres_medi_out.medi_doage,' + \
            ' medi_pass.medi_pass, CONVERT(NVARCHAR(1000),medi_pass.name) medi_pass_name,' + \
            ' pres_medi_out.unit_id, convert(NVARCHAR(1000), unit.name) unit_name,' + \
            ' pres_medi_out.medi_unit_id, convert(NVARCHAR(1000),medi_unit.name) medi_unit_name,' + \
            ' pres_medi_out.use_type_id, CONVERT(NVARCHAR(1000), use_type.name) use_type_name' + \
            ' FROM pres_medi_out INNER JOIN medi_pass ON pres_medi_out.medi_pass = medi_pass.medi_pass' + \
            ' INNER JOIN medi_unit unit ON pres_medi_out.unit_id = unit.medi_unit_id ' + \
            ' INNER JOIN medi_unit ON pres_medi_out.medi_unit_id = medi_unit.medi_unit_id ' + \
            ' INNER JOIN use_type ON pres_medi_out.use_type_id = use_type.use_type_id ' + \
            ' INNER JOIN medi_mark ON pres_medi_out.medi_mark_id = medi_mark.medi_mark_id ' + \
            ' where pres_id = \'' + pres_id + '\'' + \
            ' order by seq'
        ret['data']['list'] = db.query(sql)
    else:
        ret = {
            "code": 201,
            "msg": "获取处方记录异常"
        }
    # 关闭数据库连接
    db.close()
    return ret
    
    
    
    
    
    
